<?php
namespace app\home\model;

use think\Model;

class BorrowTender extends Model {
    public function add($data = []) {
        $data ['addtime'] = time();
        $data ['addip'] = get_client_ip();
        $this->insert($data);
        return $this->getLastInsID();
    }

    /**
     * 投资列表
     *
     * @return Array
     */
    function GetTenderList($data = []) {

        $_sql = "where 1=1 ";
        if (isset($data['tender_nid'])) {
            $_sql .= " and p1.tender_nid='{$data['tender_nid']}'";
        }
        //ahui 0328 zhaiquan
        //判断用户id
        if (isset($data['user_id'])) {
            $_sql .= " and ((p1.change_status!=1 and p1.user_id={$data['user_id']}) or (p1.change_status=1 and p1.change_userid='{$data['user_id']}'))";
        }

        //判断借款用户
        if (isset($data['borrow_userid'])) {
            $_sql .= " and p3.user_id = {$data['borrow_userid']}";
        }
        //借款协议
        if (isset($data['tender_id']) && isset($data['borrow_id'])) {
            if ($data['tender_id'] != $data['borrow_id']) {
                $_sql .= " and p1.user_id = {$data['tender_id']}";
            }
        }

        //搜到用户名
        if (isset($data['username'])) {
            $data['username'] = urldecode($data['username']);
            $_sql .= " and p2.username like '%{$data['username']}%'";
        }

        //搜索借款名称
        if (isset($data['borrow_status'])) {
            $_sql .= " and p3.`status` in ({$data['borrow_status']})";
        }

        if (isset($data['change_status'])) {
            $_sql .= " and p1.`change_status` in  ({$data['change_status']})";
        }


        //搜索借款名称
        if (isset($data['borrow_name'])) {
            $_sql .= " and p3.`name` like '%" . urldecode($data['borrow_name']) . "%'";
        }

        //搜索借款名称
        if (isset($data['borrow_nid'])) {
            $_sql .= " and p3.`borrow_nid` = '{$data['borrow_nid']}'";
        }

        //投资类型
        if (isset($data['tender_type'])) {
            if ($data['tender_type'] == "wait") {
                $_sql .= "  and p3.`status` = 3 and ((p3.borrow_type!='roam' and p3.repay_full_status=0 and p1.recover_full_status=0) or (p3.borrow_type='roam' and p3.tender_times!=p3.repay_times))  ";
            } elseif ($data['tender_type'] == "over") {
                $_sql .= "  and p3.`status` = 3 and (p3.repay_full_status=1 or (p1.recover_times=1 and p3.borrow_type='day') or(p1.recover_times=1 and p3.borrow_type='second')) ";
            }
        }

        if (isset($data['keywords']) != "") {
            $_sql .= " and (p3.name like '%" . urldecode($data['keywords']) . "%') ";
        }

        if (isset($data['time'])) {
            switch ($data['time']) {
                case 1:
                case 3:
                case 6:
                    $_sql .= " and p1.addtime <= " . strtotime('+ ' . $data['time'] . '  months') . " and p1.addtime >= " . strtotime('-' . $data['time'] . ' months ');
                    break;
                case 7:
                    $_sql .= " and p1.addtime <= " . strtotime('+ ' . $data['time'] . '  days') . " and p1.addtime >= " . strtotime('-' . $data['time'] . ' days');
                    break;
            }
        }

        //判断借款状态
        if (isset($data['status'])) {
            $_sql .= " and p8.recover_status in ({$data['status']})";
        }

        //排序
        if (isset($data['order']) && $data['order'] != "") {
            if ($data['order'] == "addtime" || $data['order'] == "order") {
                $_order = " group by p1.id order by p1.addtime desc, p1.id desc";
            } else {
                $_order = " group by p1.id order by p1.{$data['order']}";
            }
        } else {
            $_order = " group by p1.id order by p1.id desc";
        }

        $_select = " p1.*,p2.username,p8.recover_status,
        p3.name as borrow_name,p3.account as borrow_account,p3.borrow_type,p3.status as borrow_status,p3.sealstep,
        p4.username as borrow_username,p3.repay_account_wait as borrow_account_wait_all,
        p3.repay_account_interest_wait as borrow_interest_wait_all,p4.user_id as borrow_userid,p3.borrow_apr,p3.borrow_apr_extra,p3.borrow_style,p3.borrow_period,p3.borrow_account_scale,p7.name as borrow_type_name,p3.verify_time as borrow_verify_time,p3.repay_last_time,p3.borrow_success_time as borrow_start_time,p6.realname,p6.card_id,p2.phone, p3.verify_time as verify_time, p3.reverify_time";

        $prefix = config('database.prefix');
        $sql = "select SELECT from `{$prefix}borrow_tender` as p1 
				 left join `{$prefix}borrow_recover` as p8 on p1.id=p8.tender_id
				 left join `{$prefix}users` as p2 on p1.user_id=p2.user_id
				 left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid
				 left join `{$prefix}borrow_type` as p7 on p7.nid=p3.borrow_type
				 left join `{$prefix}users` as p4 on p4.user_id=p3.user_id
                 left join `{$prefix}users_info` as p5 on p5.user_id=p1.user_id
                 left join `{$prefix}approve_realname` as p6 on p6.user_id=p1.user_id
				 SQL ORDER LIMIT
				";
        //是否显示全部的信息
        if (isset($data['limit'])) {
            if ($data['limit'] != "all") {
                $_limit = "  limit " . $data['limit'];
            } else {
                $_limit = "";
            }
            $list = db()->query(str_replace(array('SELECT', 'SQL', 'ORDER', 'LIMIT'), array($_select, $_sql, $_order, $_limit), $sql));
            foreach ($list as $key => $value) {
                $list[$key]["username_encode"] = hash("sha256", $value["username"]);
                $list[$key]['unum'] = (count($list) - 1);
                $period_name = "个月";
                if ($value["borrow_type"] == "day") {
                    $period_name = "天";
                }
                $list[$key]["borrow_period_name"] = $value["borrow_period"] . $period_name;
                if ($value['borrow_type'] == "roam") {
                    $list[$key]['repay_last_time'] = strtotime("{$value["borrow_period"]} month", $value['addtime']);;
                }
                //借款期限
                if ($value['borrow_type'] == "roam" || $value['borrow_type'] == "day" || $value['borrow_type'] == "second") {
                    $list[$key]["_borrow_period"] = 1;
                } else {
                    $list[$key]["_borrow_period"] = $value["borrow_period"];
                }
            }
            return $list;
        }
        //分页返回结果
        $data['page'] = !isset($data['page']) ? 1 : $data['page'];
        $data['epage'] = !isset($data['epage']) ? 10 : $data['epage'];

        $_limit = " limit " . ($data['epage'] * ($data['page'] - 1)) . ", {$data['epage']}";
        $list = db()->query(str_replace(array('SELECT', 'SQL', 'ORDER', 'LIMIT'), array($_select, $_sql, $_order, $_limit), $sql));
        foreach ($list as $key => $value) {
            $repaysql = "select * from `{$prefix}borrow_repay` where repay_time<" . time() . " and repay_status=0 and borrow_nid={$value['borrow_nid']}";
            $repayresult = db()->query($repaysql);
            if ($repayresult) {
                $list[$key]['change_no'] = 1;
            }
            $period_name = "个月";
            if ($value["borrow_type"] == "day") {
                $period_name = "天";
            }
            $list[$key]["borrow_apr_1"] = $value["borrow_apr"];

            $list[$key]["borrow_period_name"] = $value["borrow_period"] . $period_name;

            if ($value['borrow_type'] == "roam") {
                $list[$key]['repay_last_time'] = strtotime("{$value["borrow_period"]} month", $value['addtime']);;
            }

            //借款期限
            if ($value['borrow_type'] == "roam" || $value['borrow_type'] == "day" || $value['borrow_type'] == "second") {
                $list[$key]["_borrow_period"] = 1;
            } else {
                $list[$key]["_borrow_period"] = $value["borrow_period"];
            }
        }
        return $list;
    }

    //待回款统计
    public function wait($data = []) {
        $prefix = config('database.prefix');
        //统计已赚1,已经回收完的，2，没有转让成功所得的已收款，3转让成功后所得的期数的已收款
        $_where = " where ((p1.user_id='{$data['user_id']}' and p2.change_status!=1 ) or (p1.user_id='{$data['user_id']}' and p2.change_status=1 and p1.recover_period<=p3.borrow_period-p2.change_period) or (p2.change_userid='{$data['user_id']}' and p2.change_status=1 and p1.recover_period>p3.borrow_period-p2.change_period)) and p1.recover_status = 0";

        $sql = "select p1.recover_status,sum(p1.recover_account) as anum,sum(p1.recover_capital) as cnum,sum(p1.recover_interest) as inum,sum(p1.recover_interest_yes) as iynum,sum(p1.recover_account_yes) as aynum,sum(p1.recover_capital_yes) as cynum,count(1) as num,count(distinct p1.borrow_nid) as times  from  `{$prefix}borrow_recover` as p1 
        left join `{$prefix}borrow_tender` as p2 on p1.tender_id=p2.id 
        left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid 
         {$_where}";
        $result = db()->query($sql);

        return $_result = [
                'total' => $result[0]['anum'],
                'account' => $result[0]['cnum'],
                'interest' => $result[0]['inum'],
        ];
    }

    function GetTenderPeople($data = []) {
        $prefix = config('database.prefix');
        $sql = 'select count(distinct user_id) total from `' . $prefix . 'borrow_tender` where borrow_nid = "' . $data['borrow_nid'] . '"';
        $result = db()->query($sql);
        return $result ? $result[0]['total'] : 0;
    }

    function GetTenderCount($data = []) {
        $prefix = config('database.prefix');
        $sql = 'select count(*) total_count, count(distinct user_id) total_people from `' . $prefix . 'borrow_tender` where borrow_nid = "' . $data['borrow_nid'] . '"';
        $result = db()->query($sql);
        return $result ? $result[0] : null;
    }

    /**
     * 查看投资标
     *
     * @param Array $data = array("id"=>"投资序号","tender_nid"=>"投资标识名");
     * @return Array
     */
    public static function GetTenderOne($data = array()) {
        $_sql = "where 1=1 ";

        if (isset($data['id'])) {
            $_sql .= " and  p1.id = '{$data['id']}' ";
        }

        if (isset($data['tender_id'])) {
            $_sql .= " and p1.tender_id = '{$data['tender_id']}' ";
        }
        if (isset($data['tender_nid'])) {
            $_sql .= " and p1.nid='{$data['tender_nid']}'";
        }
        if (isset($data['nid'])) {
            $_sql .= " and p1.borrow_nid='{$data['nid']}'";
        }
        $prefix = config('database.prefix');
        $_select = " p1.*,p2.username,p3.borrow_type,p3.name,p3.reverify_time,p3.repay_account_interest,p3.name as borrow_name,p3.borrow_end_time,p3.account as borrow_account,
        p3.borrow_period,p3.borrow_style,p3.borrow_use,p3.borrow_flag,p3.borrow_apr,p3.borrow_success_time,p4.realname,p4.phone,p5.card_id,p3.borrow_apr_extra";
        $sql = "select $_select from `{$prefix}borrow_tender` as p1 
				 left join `{$prefix}users` as p2 on p1.user_id=p2.user_id
				 left join `{$prefix}borrow` as p3 on p1.borrow_nid=p3.borrow_nid
                 left join `{$prefix}users_info` as p4 on p1.user_id=p4.user_id
                 left join `{$prefix}approve_realname` as p5 on p1.user_id=p5.user_id
				 {$_sql}";
        $ex = db()->query($sql);
        if (!$ex) {
            return null;
        }
        $result = $ex[0];
        $style = model('borrow_style')->getOne(['nid' => $result['borrow_style'], 'status' => 1]);
        $result['style_title'] = $style['name'];//还款方式
        $result['last_date'] = (strtotime(date('Y-m-d', $result['borrow_end_time'])) - strtotime(date('Y-m-d'))) / (24 * 60 * 60);//到期时间
        if ($result['reverify_time'] > 0) {
            $result['end_time'] = $result['borrow_type'] == "day" ? strtotime(date("Y-m-d", $result['reverify_time']) . " +" . intval($result['borrow_period']) . " day") : strtotime(date("Y-m-d", $result['reverify_time']) . " +" . intval($result['borrow_period']) . " month");
            $result['end_time'] = date('Y-m-d H:i:s', $result['end_time']);
        } else {
            $result['end_time'] = '-';
        }

        $coupon = model('coupon_users')->getOne([
                'user_id' => $result['user_id'],
                'borrow_nid' => $result['borrow_nid'],
                'borrow_tender_id' => $result['id'],
        ]);
        if ($coupon) {
            if ($coupon['style'] == 1) {
                $total_interest = model('borrow_calculate', 'logic')->GetType([
                        'account' => $result['account'],
                        'period' => $result['borrow_period'],
                        'apr' => $result['borrow_apr'] + $coupon['interest'],
                        'style' => $result['borrow_style'],
                        'borrow_nid' => $result['borrow_nid'],
                        'type' => 'all',
                ]);
            } else {
                $total_interest = model('borrow_calculate', 'logic')->GetType([
                        'account' => $result['account'] + $coupon['money'],
                        'period' => $result['borrow_period'],
                        'apr' => $result['borrow_apr'],
                        'style' => $result['borrow_style'],
                        'borrow_nid' => $result['borrow_nid'],
                        'type' => 'all',
                ]);
            }
            $result['recover_account_interest'] = $total_interest['interest_total'];
        }

        $period_name = $result["borrow_type"] == "day" ? "天" : "个月";
        $result["borrow_period_name"] = $result["borrow_period"] . $period_name;
        return $result;
    }

    //收款明细
    function GetRecoverList($data) {

        $_sql = " where 1=1 ";
        //1，未转让成功，且是他的投资
        //2,
        if (isset($data['user_id'])) {
            $_sql .= " and ((p5.change_status!=1 and p1.user_id={$data['user_id']}) or (p1.user_id='{$data['user_id']}' and p5.change_status=1 and p1.recover_period<=p2.borrow_period-p5.change_period) or (p5.change_status=1 and p5.change_userid={$data['user_id']} and p1.recover_period>p2.borrow_period-p5.change_period) or (p5.user_id='{$data['user_id']}' and p2.borrow_type='day' and p5.change_status=1 ) )";
        }

        //借款协议
        if (isset($data['tender_id']) && isset($data['borrow_id'])) {
            if ($data['tender_id'] != $data['borrow_id']) {
                $_sql .= " and p1.user_id = {$data['tender_id']}";
            }
        }
        if (isset($data['tender_id'])) {
            $_sql .= " and p1.tender_id = {$data['tender_id']}";
        }
        if (isset($data['status'])) {
            $_sql .= " and p1.status={$data['status']}";
        }
        if (isset($data['recover_status'])) {
            if ($data['recover_status'] == 2) {
                $_sql .= " and p1.recover_status=0";
            } else {
                $_sql .= " and p1.recover_status={$data['recover_status']}";
            }
        }
        if (isset($data['borrow_status'])) {
            $_sql .= " and p2.status={$data['borrow_status']}";
        }
        if (isset($data['username'])) {
            $_sql .= " and p3.username like '%{$data['username']}%' ";
        }
        //搜索标题
        if (isset($data['borrow_name'])) {
            $data['borrow_name'] = urldecode($data['borrow_name']);
            $_sql .= " and p2.name like '%{$data['borrow_name']}%' ";
        }
        //搜索贷款号
        if (isset($data['borrow_nid'])) {
            $_sql .= " and p1.borrow_nid = {$data['borrow_nid']}";
        }
        //搜索贷款类型
        if (isset($data['borrow_type'])) {
            $_sql .= " and p2.borrow_type='{$data['borrow_type']}'";
        }

        if (isset($data['web'])) {
            $_sql .= " and p6.web_status=2";
        }

        if (isset($data['late'])) {
            $_sql .= " and (p1.recover_time < " . time() . " and p1.recover_status!=1) or (p1.recover_status=1 and p1.late_days>0)";
        }


        if (isset($data['yestime1'])) {
            $yestime1 = ($data['yestime1'] == "request") ? $_REQUEST['yestime1'] : $data['yestime1'];
            if ($yestime1 != "") {
                $_sql .= " and p1.recover_yestime > " . get_mktime($yestime1);
            }
        }

        if (isset($data['yestime2'])) {
            $yestime2 = ($data['yestime2'] == "request") ? $_REQUEST['yestime2'] : $data['yestime2'];
            if ($yestime2 != "") {
                $_sql .= " and p1.recover_yestime < " . get_mktime($yestime2);
            }
        }

        if (isset($data['type'])) {
            if ($data['type'] == "yes") {
                $_sql .= " and p1.recover_status =1 or p1.recover_web=1";
            } elseif ($data['type'] == "wait") {
                $_sql .= " and p1.recover_status !=1 and p1.recover_web!=1";
            } elseif ($data['type'] == "web") {
                $_sql .= " and p1.recover_web=1";
            }
        }
        if (isset($data['time'])) {
            switch ($data['time']) {
                case 1:
                case 3:
                case 6:
                    $_sql .= " and p1.recover_time <= " . strtotime('+ ' . $data['time'] . '  months') . " and p1.recover_time >= " . strtotime('-' . $data['time'] . ' months ');
                    break;
                case 7:
                    $_sql .= " and p1.recover_time <= " . strtotime('+ ' . $data['time'] . '  days') . " and p1.recover_time >= " . strtotime('-' . $data['time'] . ' days');
                    break;
            }
        }

        if (isset($data['keywords']) != "") {
            $_sql .= " and (p2.name like '%" . urldecode($data['keywords']) . "%') ";
        }
        $_order = " order by p2.id ";
        if (isset($data['order']) != "") {
            if ($data['order'] == "repay_time") {
                $_order = " order by p1.recover_time asc";
            } elseif ($data['order'] == "order") {
                $_order = " order by p1.recover_time desc ";
            } elseif ($data['order'] == "recover_status") {
                $_order = " order by p1.`recover_status` asc,p1.id desc ";
            }
        }

        if (isset($data['protocol']) && $data['protocol'] == 1) {
            $_select = 'p1.recover_period,p1.recover_time,sum(p1.recover_account) as recover_account,sum(p1.recover_capital) as recover_capital,sum(p1.recover_interest) as recover_interest';
            $_order = " order by p1.`recover_period` asc ";
            $group = "group by p1.recover_period";
        } else {
            $_select = 'p1.*,p6.name as borrow_type_name,p6.title as type_title,p1.recover_account_yes as recover_recover_account_yes,p2.name as borrow_name,p2.borrow_period,p2.borrow_type,p2.borrow_apr,p3.username,p4.username as borrow_username,p4.user_id as borrow_userid,p5.recover_account_yes as tender_recover_account_yes';
            $group = "";
        }
        $prefix = config('database.prefix');
        $sql = "select SELECT from `{$prefix}borrow_recover` as p1 
				left join `{$prefix}borrow` as p2 on  p2.borrow_nid = p1.borrow_nid
				left join `{$prefix}users` as p3 on  p3.user_id = p1.user_id
				left join `{$prefix}users` as p4 on  p4.user_id = p2.user_id
				left join `{$prefix}borrow_tender` as p5 on  p1.tender_id = p5.id
				left join `{$prefix}borrow_type` as p6 on  p6.nid = p2.borrow_type
			   {$_sql} $group ORDER LIMIT";
        //是否显示全部的信息
        if (isset($data['limit'])) {
            $_limit = "";
            if ($data['limit'] != "all") {
                $_limit = "  limit " . $data['limit'];
            }
            $list = db()->query(str_replace(array('SELECT', 'ORDER', 'LIMIT'), array($_select, $_order, $_limit), $sql));
            return $list;
        }


        $page = !isset($data['page']) ? 1 : $data['page'];
        $epage = !isset($data['epage']) ? 10 : $data['epage'];
        $index = $epage * ($page - 1);
        $limit = " limit {$index}, {$epage}";
        $list = db()->query(str_replace(array('SELECT', 'ORDER', 'LIMIT'), array($_select, $_order, $limit), $sql));
        foreach ($list as $key => $value) {
            $type_name = "";
            if ($value["recover_type"] == "advance") {
                $type_name = "提前还款";
            } elseif ($value["recover_type"] == "yes") {
                $type_name = "正常还款";
            } elseif ($value["recover_type"] == "late") {
                $type_name = "逾期还款";
            } elseif ($value["recover_type"] == "web") {
                $type_name = "网站垫付";
            } elseif ($value["recover_type"] == "wait") {
                $type_name = "未还";
            }
            $days = $this->GetDays(array("repay_time" => $value["recover_time"], "repay_yestime" => $value["recover_yestime"]));
            if ($days > 0) {
                $list[$key]['late_days'] = $days;
            }
            if ($value["borrow_type"] == "roam") {
                $list[$key]["borrow_period"] = 1;
            }
            $list[$key]["recover_type_name"] = $type_name;

            $list[$key]["recover_time"] = $value['recover_yestime'] ? date('Y-m-d H:i:s', $value['recover_yestime']) : date('Y-m-d H:i:s', $value['recover_time']);
        }

        return $list;
    }

    function GetDays($data = array()) {
        if (!isset($data["now_time"])) {
            $data["now_time"] = time();
        }
        $_repay_time = get_mktime(date("Y-m-d", $data["repay_time"]));
        $_repay_yestime = get_mktime(date("Y-m-d", $data["repay_yestime"]));
        $_now_time = get_mktime(date("Y-m-d", $data["now_time"]));
        if ($data["repay_yestime"] != "") {
            $late_days = ($_repay_yestime - $_repay_time) / (60 * 60 * 24);
            if ($late_days < 0) {
                $late_days = 0;
            }
        } else {
            $late_days = ($_now_time - $_repay_time) / (60 * 60 * 24);
        }

        return $late_days;
    }
}